﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using System.Linq;
using SqlSugar;
using UtilsBase;
using UtilsCache;
//using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UtilsORM
{
    public class MySqlYihongIot
    {
        static string connectionString = UtilAppsettingCache.getSetting("MySqlserver:ConnectionString_yihongIOT");// Configini.Configini.getConnectionStringValue("MySQL");
        /// <summary>
        /// 获取数据库连接实体，禁止使用静态变量对接
        /// SqlSugarScope支持线程安全 
        /// </summary>
        public SqlSugarClient Db
        {

            get
            {

                return new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = connectionString,//连接符字串
                    DbType = SqlSugar.DbType.MySql,//数据库类型
                    IsAutoCloseConnection = true, //不设成true要手动close
                                                  // InitKeyType = InitKeyType.Attribute  //从实体特性中读取主键自增列信息
                },
                db =>
                {
                    //(A)全局生效配置点
                    //调试SQL事件，可以删掉
                    db.Aop.OnLogExecuting = (sql, pars) =>
                        {
                          //  Console.WriteLine(sql);
                            //输出sql,查看执行sql
                            //5.0.8.2 获取无参数化 SQL
                            //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
                        };
                });

            }

        }
        /// <summary>
        /// 模式： 单例模式   SqlSugarScope一直new会内存泄露 一定要用单例
        /// </summary>
        public static SqlSugarScope DbScope
        {
            get
            {
                return new SqlSugarScope(new ConnectionConfig()
                {
                    ConnectionString = connectionString,//连接符字串
                    DbType = SqlSugar.DbType.SqlServer,//数据库类型
                    IsAutoCloseConnection = true, //不设成true要手动close
                                                  // InitKeyType = InitKeyType.Attribute  //从实体特性中读取主键自增列信息
                },
                db =>
                {
                    //(A)全局生效配置点
                    //调试SQL事件，可以删掉
                    // db.Aop.OnLogExecuting = (sql, pars) =>
                    // {
                    //  Console.WriteLine(sql);
                    //输出sql,查看执行sql
                    //5.0.8.2 获取无参数化 SQL
                    //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
                    // };
                });
            }
        }

        public static DataTable GetDataTable(string sql, object parameters)
        {
            return new MySqlYihongIot().Db.Ado.GetDataTable(sql, parameters);
        }
        public static DataTable GetDataTable(string sql)
        {
            return new MySqlYihongIot().Db.Ado.GetDataTable(sql);
        }
        public static List<T> GetList<T>(string sql)
        {
            return new MySqlYihongIot().Db.Ado.SqlQuery<T>(sql);
        }
        public static List<T> GetList<T>(string sql, object parameters)
        {
            return new MySqlYihongIot().Db.Ado.SqlQuery<T>(sql, parameters);
        }
        public static int ExecuteCommand(string sql)
        {
            return new MySqlYihongIot().Db.Ado.ExecuteCommand(sql);
        }
        public static int ExecuteCommand(string sql, object parameters)
        {
            return new MySqlYihongIot().Db.Ado.ExecuteCommand(sql, parameters);
        }
        public static Task<int> ExecuteCommandAsync(string sql)
        {
            return new MySqlYihongIot().Db.Ado.ExecuteCommandAsync(sql);
        }
        public static DataTable getPageDataTable(int pageIndex, int pageSize, string sqlquery, List<SugarParameter> sugarParameters, string orderBy, ref int totalRecord)
        {

            DataTable dt = new DataTable();
            DataRow dr;
            var result = new MySqlYihongIot().Db.SqlQueryable<dynamic>(sqlquery).AddParameters(sugarParameters).OrderBy(orderBy).ToPageList(pageIndex, pageSize, ref totalRecord);
            for (int i = 0; i < result.Count; i++)
            {
                if (i == 0)
                {
                    //提示缺少编译器需要成员'microsoft.csharp.runtimebinder.binder.convert' 您需要添加引用Microsoft.CSharp
                    foreach (KeyValuePair<string, object> col in result[i])
                    {
                        dt.Columns.Add(col.Key);
                    }
                }

                dr = dt.NewRow();
                foreach (KeyValuePair<string, object> col in result[i])
                {
                    dr[col.Key] = col.Value;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

    }
}
